package com.example.wenhua.mapper;

import com.example.wenhua.entity.Course;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.math.BigDecimal;

/**
 * 课程数据访问层
 */
@Mapper
public interface CourseMapper {
    
    /**
     * 查询所有课程
     */
    @Select("SELECT * FROM c")
    List<Course> findAll();
    
    /**
     * 根据筛选条件查询课程（使用XML映射文件）
     */
    List<Course> findWithFilter(@Param("cno") String cno, @Param("cname") String cname, @Param("teacher") String teacher);
    
    /**
     * 根据课程号查询课程
     */
    @Select("SELECT * FROM c WHERE cno = #{cno}")
    Course findByCno(String cno);
    
    /**
     * 根据教师名查询课程
     */
    @Select("SELECT * FROM c WHERE teacher = #{teacher}")
    List<Course> findByTeacher(String teacher);
    
    /**
     * 新增课程
     */
    @Insert("INSERT INTO c(cno, cname, teacher, credit) VALUES(#{cno}, #{cname}, #{teacher}, #{credit})")
    int insert(Course course);
    
    /**
     * 更新课程信息
     */
    @Update("UPDATE c SET cname=#{cname}, teacher=#{teacher}, credit=#{credit} WHERE cno=#{cno}")
    int update(Course course);
    
    /**
     * 删除课程
     */
    @Delete("DELETE FROM c WHERE cno = #{cno}")
    int delete(String cno);
    
    /**
     * 查询课程选课人数
     */
    @Select("SELECT COUNT(*) FROM sc WHERE cno = #{cno}")
    Integer getStudentCountByCno(String cno);
    
    /**
     * 查询课程平均分
     */
    @Select("SELECT COALESCE(AVG(grade), 0) FROM sc WHERE cno = #{cno} AND grade IS NOT NULL")
    BigDecimal getAverageGradeByCno(String cno);
    
    /**
     * 查询课程不及格学生数量
     */
    @Select("SELECT COUNT(*) FROM sc WHERE cno = #{cno} AND grade < 60")
    Integer getFailCountByCno(String cno);
    
    /**
     * 查询所有课程的整体平均成绩
     */
    @Select("SELECT COALESCE(AVG(grade), 0) FROM sc WHERE grade IS NOT NULL")
    BigDecimal getOverallAverageScore();
}